package com.boilerCloud.dao.device;

import java.util.ArrayList;
import java.util.List;

import org.springframework.stereotype.Repository;

import com.alibaba.fastjson.JSONObject;
import com.boilerCloud.dao.BaseDao;
import com.boilerCloud.dto.HzlqswPage;
import com.boilerCloud.dto.device.DeviceInfo;
import com.boilerCloud.dto.device.DeviceWarnRecord;
import com.boilerCloud.util.StringUtil;
@Repository
public class DeviceDao extends BaseDao {

	public void qryList(DeviceInfo device, HzlqswPage<DeviceInfo> page) throws Exception {
		String countsql = "select count(*) from d_device_info a "
				+ " left join d_model_info b on b.id = a.model_id"
				+ " left join project_info d on d.id = a.project_id" 
				+ " left join app_user e on a.user_id = e.id" 
				+ " left join app_user c on e.dealer_id = c.id" 
				+ " where a.is_delete='n' ";
			
		String listSql = "select a.*,b.name modelName,b.pic_url picUrl,d.name projectName , e.name username "
					+ " from d_device_info a "
					+ " left join d_model_info b on b.id = a.model_id"
					+ " left join project_info d on d.id = a.project_id" 
					+ " left join app_user e on a.user_id = e.id" 
					+ " left join app_user c on e.dealer_id = c.id" 
					+ " where a.is_delete='n' ";
		List<Object> params= new ArrayList<Object>();
		if(!StringUtil.isEmpty(device.getName())){
			countsql += " and a.name like ?";
			listSql += " and a.name like ?";
			params.add("%" + device.getName() +"%");
		}
		if(!StringUtil.isEmpty(device.getSerialNumber())){
			countsql += " and a.serial_number like ?";
			listSql += " and a.serial_number like ?";
			params.add("%"+device.getSerialNumber()+"%");
		}
		if(device.getModelId() != -1 && device.getModelId() != 0){
			countsql += " and a.model_id = ?";
			listSql += " and a.model_id = ?";
			params.add(device.getModelId());
		}
		if(device.getProjectId() != -1 && device.getProjectId() != 0){
			countsql += " and a.project_id = ?";
			listSql += " and a.project_id = ?";
			params.add(device.getProjectId());
		}
		if (!StringUtil.isEmpty(device.getProjectIdNoBind())&&device.getProjectIdNoBind()==-1){
			if(!StringUtil.isEmpty(device.getProjectId())){
				countsql += " or ";
				listSql += " or ";
			}else{
				countsql += " and ";
				listSql += "and ";
			}
			countsql += "  (a.project_id is null or a.project_id = 0 )";
			listSql += "  (a.project_id is null or a.project_id = 0 ) ";
			//params.add(device.getProjectId());
		}
		if(device.getTypeId() != 0 && device.getTypeId() != -1){
			countsql += " and a.type_id = ?";
			listSql += " and a.type_id = ?";
			params.add(device.getTypeId());
		}
		
		if(device.getUserId() != 0){
			countsql += " and a.user_id = ?";
			listSql += " and a.user_id = ?";
			params.add(device.getUserId());
		}
		if(device.getDealerId() != 0){
			countsql += " and c.id = ?";
			listSql += " and c.id = ?";
			params.add(device.getDealerId());
		}
		listSql += " order by id desc ";
		this.queryListByPage(countsql, params, listSql, params, page, DeviceInfo.class);
		
	}

	public void edit(DeviceInfo device) throws Exception{
		String sql = "update d_device_info set modify_time=now() " ;
		List<Object> param = new ArrayList<Object>();
		if (!StringUtil.isEmpty(device.getName())) {
			sql+=",name=?";
			param.add(device.getName());
		}	
		if (!StringUtil.isEmpty(device.getSerialNumber())) {
			sql+=",serial_number=?";
			param.add(device.getSerialNumber());
		}	
		//if (!StringUtil.isEmpty(device.getCommSerialNumber())) {
			sql+=",comm_serial_number=?";
			param.add(device.getCommSerialNumber());
		//}
		if(!StringUtil.isEmpty(device.getCommType())){
			sql+=",comm_type=?";
			param.add(device.getCommType());
			
		}
		
		if(!StringUtil.isEmpty(device.getStatus())){
			sql+=",status=?";
			param.add(device.getStatus());
			
		}
		if(!StringUtil.isEmpty(device.getMark())){
			sql+=",mark=?";
			param.add(device.getMark());
			
		}
		if(!StringUtil.isEmpty(device.getOrderNo())){
			sql+=",order_no=?";
			param.add(device.getOrderNo());
			
		}
		if (!StringUtil.isEmpty(device.getModelId())) {
			sql+=",model_id=?";
			param.add(device.getModelId());
		}		
		if (!StringUtil.isEmpty(device.getLongitude())) {
			sql+=",longitude=?";
			param.add(device.getLongitude());
		}		
		if (!StringUtil.isEmpty(device.getLatitude())) {
			sql+=",latitude=?";
			param.add(device.getLatitude());
		}		
		
		if (!StringUtil.isEmpty(device.getProjectId())&&device.getProjectId()!=-1) {
			sql+=",project_id=?";
			param.add(device.getProjectId());
		}else{
			sql+=",project_id='0'";
		}
		sql += ",box_id=? ";
		param.add(device.getBoxId());
		
		sql+=" where id=?";
		param.add(device.getId());
		this.jdbcTemplate.update(sql,param.toArray());
	}

	public void del(int id) {
		String sql = "update d_device_info set is_delete='y' where id=?";
		this.jdbcTemplate.update(sql,id);
		
	}

	public void add(DeviceInfo device)throws Exception {
		String sql = "insert into d_device_info(order_no,name,"
				+ "model_id,longitude,latitude,mark,serial_number,"
				+ "comm_serial_number,comm_type,status,create_time,modify_time,is_delete,box_id)"
				+ " values(?,?,?,?,?,?,?,?,?,?,now(),now(),'n',?)";
		List<Object> param = new ArrayList<Object>();
		param.add(device.getOrderNo());
		param.add(device.getName());
		param.add(device.getModelId());
		param.add(device.getLongitude());
		param.add(device.getLatitude());
		param.add(device.getMark());
		param.add(device.getSerialNumber());
		param.add(device.getCommSerialNumber());
		param.add(device.getCommType());
		param.add(device.getStatus());
		param.add(device.getBoxId());
		this.jdbcTemplate.update(sql,param.toArray());
		
	}

	public DeviceInfo getDeviceInfo(int id)throws Exception {
		String sql = "select * from d_device_info where id = ? limit 1";
		return this.queryForObject(sql, DeviceInfo.class, id);
		
	}

	public List<DeviceInfo> getDeviceList(DeviceInfo device)throws Exception {
		String sql = "select a.*,b.name modelName,b.pic_url picUrl,"
				+ "ifnull(e.name,'未分配') username "
				+ " from d_device_info a "
				+ " left join d_model_info b on b.id = a.model_id"
				+ " left join app_user e on a.user_id = e.id" 
				+ " left join app_user c on e.dealer_id = c.id" 
				+ " where a.is_delete= 'n'";
		List<Object> params = new ArrayList<Object>();
		if(device.getUserId() != 0){
			sql += " and a.user_id = ?";
			params.add(device.getUserId());
		}
		if(device.getDealerId() != 0){
			sql += " and c.id = ?";
			params.add(device.getDealerId());
		}
		return this.queryForList(sql, DeviceInfo.class,params.toArray());
	}

	public List<String> qryBoxIds() throws Exception {
		String sql = "select comm_serial_number from d_device_info where box_id > char(0)";
		return this.queryForList(sql, String.class);
	}

	public void updateState(int state, String box_id) {
		String sql = "update d_device_info set state=? where box_id =?";
		this.jdbcTemplate.update(sql,state,box_id);
		
	}

	public List<DeviceInfo> qrydeviceBoxIds() throws Exception {
		String sql = "select * from d_device_info where box_id > char(0)";
		return this.queryForList(sql, DeviceInfo.class);
	}

	public void addWarnInfo(JSONObject object, int id) throws Exception{
		String sql = "insert into d_device_warn_info_record(name,content,value,status,event_time,create_time,is_show,device_id,n)"
				+ " values(?,?,?,?,?,now(),'y',?,?)";
		List<Object> params =new ArrayList<Object>();
		params.add(object.getString("rn")+"");
		params.add(object.getString("m")+"");
		params.add(object.getString("v")+"");
		params.add(object.getIntValue("a"));
		params.add(object.getLong("t"));
		params.add(id);
		params.add(object.getIntValue("n"));
		
		this.jdbcTemplate.update(sql,params.toArray());
		
	}

	public void updateQueryTime(long time, int id) {
		String sql = "update d_device_info set query_time = ? where id=?";
		this.jdbcTemplate.update(sql,time,id);
		
	}

	public void updateShow(int intValue, int id) {
		String sql ="update d_device_warn_info_record set is_show ='n' where device_id=? and n=?";
		this.jdbcTemplate.update(sql,id,intValue);
		
	}


	public  List<DeviceWarnRecord> getCurrAlarmRecordList(int device_id)throws Exception {
		String sql = "SELECT MAX(a.id),a.name,a.event_time,a.content,a.value,a.status"
				+ " FROM d_device_warn_info_record a"
				+ " WHERE a.STATUS = 1 AND a.is_show ='y' and a.device_id=? GROUP BY a.NAME";
		return this.queryForList(sql,DeviceWarnRecord.class,device_id);
		
	}

	public Object getAlarmRecordList(int device_id)throws Exception {
		String sql = "SELECT a.name,a.event_time,a.content,a.value,a.status"
				+ " FROM d_device_warn_info_record a"
				+ " WHERE  a.device_id=? order BY a.id ";
		return this.queryForList(sql,DeviceWarnRecord.class,device_id);
	}

	
}
